package com.futvan.z.system.zreport;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Service;

import com.futvan.z.framework.common.bean.Code;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.framework.core.SuperService;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.BeanUtil;
import com.futvan.z.framework.util.StringUtil;
import com.futvan.z.framework.util.SystemUtil;
@Service
public class ZreportService extends SuperService{
	/**
	 * 插入数据
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result insertData(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		bean.put("zid", z.newZid("z_report"));
		//生成字段
		CreateReportColumn(bean);
		//生成默认按钮
		CreateReportButton(bean);
		//创建菜单
		CreateReportMenu(bean);
		return insert(bean,request);
	}

	/**
	 * 修改数据 
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result updateData(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//生成字段
		CreateReportColumn(bean);
		//生成默认按钮
		CreateReportButton(bean);
		//创建菜单
		CreateReportMenu(bean);
		return update(bean,request);
	}

	private void CreateReportMenu(HashMap<String, String> bean) {
		String reportid = String.valueOf(bean.get("zid"));
		int MenuCount = selectInt("SELECT COUNT(*) FROM z_menu WHERE reportid = '"+reportid+"'");
		if(MenuCount==0) {
			StringBuffer menusql = new StringBuffer();
			menusql.append("INSERT INTO z_menu(zid,parentid,name,url,menu_type,reportid,menu_icon) VALUES");
			String zid = z.newZid("z_menu");
			menusql.append(" ('"+zid+"','00000000000000000000000000000000','"+bean.get("title")+"','rlist?zid="+reportid+"','reportmenu','"+reportid+"','fa fa-bar-chart-o'); ");
			insert(menusql.toString());
			
			
			//添加按钮权限数据
			List<HashMap<String,String>> buttonList = selectList("SELECT * FROM z_report_button WHERE pid = '"+reportid+"'");
			for (HashMap<String,String> rbutton : buttonList) {
				//添加按钮权限
				String buttonSQL = "INSERT INTO z_menu_report_button(zid,pid,buttonId) VALUES ('"+z.newZid("z_menu_report_button")+"','"+zid+"','"+rbutton.get("zid")+"')";
				insert(buttonSQL);
			}
			
			
		}

	}

	/**
	 * 生成字段
	 * @param bean
	 * @throws Exception 
	 */
	private void CreateReportColumn(HashMap<String,String> bean) throws Exception {
		//解析SQL获取字段
		String sql = bean.get("sqlinfo");
		if(z.isNotNull(sql)) {

			//获取已创建的字段
			Map<String,String> old_column_map = getOldReportColumn(bean.get("zid"));

			//根据SQL解析创建字段
			List<String> columnList = AnalysisSQL(bean,StringUtil.parseExpression(sql, bean));
			Map<String,String> analysisSqlColumnMap = new HashMap<String, String>();
			for (String columnId : columnList) {
				//判读是否已经创建了字段
				if(old_column_map.get(columnId)==null || "".equals(old_column_map.get(columnId))) {
					//创建字段
					z_report_column col = new z_report_column();
					col.setColumn_id(columnId);
					col.setColumn_name(columnId);
					col.setColumn_type("0");
					col.setColumn_size("3");
					col.setColunm_length_list("150");
					col.setCompare("1");
					col.setPid(bean.get("zid"));
					HashMap<String,String> ColumnBean = BeanUtil.BeanToMap(col);
					ColumnBean.put("tableId", "z_report_column");
					Result result = insert(ColumnBean,null);
					if(!Code.SUCCESS.equals(result.getCode())) {
						throw new Exception("报表管理，创建字段出错");
					}
				}

				analysisSqlColumnMap.put(columnId, columnId);
			}

			//删除多余字段-遍历old_column_map
			for (String key : old_column_map.keySet()){
				if(!z.isNotNull(analysisSqlColumnMap.get(key))) {
					//删除
					delete("delete from z_report_column where pid = '"+bean.get("zid")+"' and column_id = '"+key+"'");
				}
			} 

		}else {
			throw new Exception("报表管理生成字段出错，SQL不能为空");
		}
	}

	/**
	 * 获取已生成的字段
	 * @param pid
	 * @return
	 */
	private Map<String, String> getOldReportColumn(String pid) {
		Map<String,String> columnMap = new HashMap<String,String>();
		List<HashMap<String,String>> old_column_list = selectList("SELECT column_id FROM z_report_column WHERE pid = '"+pid+"'");
		for (HashMap<String,String> columnId : old_column_list) {
			columnMap.put(columnId.get("column_id"), columnId.get("column_id"));
		}
		return columnMap;
	}

	/**
	 * 创建默认按钮
	 * @param bean
	 */
	public void CreateReportButton(HashMap<String, String> bean) {
		//添加是否添加查询按钮
		int select_button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+bean.get("zid")+"' and button_id = 'select_button'");
		if(select_button_count==0) {
			String zid = z.newZid("z_form_table_button");
			String pid = String.valueOf(bean.get("zid"));
			String button_id = "select_button";
			StringBuffer select_button_sql = new StringBuffer();
			select_button_sql.append("INSERT INTO z_report_button ");
			select_button_sql.append(" (zid, ");
			select_button_sql.append(" pid, ");
			select_button_sql.append(" js_onclick, ");
			select_button_sql.append(" button_id, ");
			select_button_sql.append(" is_hidden, ");
			select_button_sql.append(" button_icon, ");
			select_button_sql.append(" button_name) ");
			select_button_sql.append(" VALUES ( ");
			select_button_sql.append(" '"+zid+"', ");
			select_button_sql.append(" '"+pid+"', ");
			select_button_sql.append(" 'openSelectWindows();', ");
			select_button_sql.append(" '"+button_id+"', ");
			select_button_sql.append(" '0', ");
			select_button_sql.append(" 'fa fa-search', ");
			select_button_sql.append(" '查询') ");
			insert(select_button_sql.toString());
		}
	}

}
